package DB;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Random;

import DB.DatabaseTables.Table;

public class categoryConnector{
	
	private DatabaseTables tablesStructs = DatabaseTables.getInstance();
	private static categoryConnector instance = null;
	public final static String freeBaseURL = "http://download.freebase.com/datadumps/latest/browse/fictional_universe/";
	ArrayList<freebaseTable> tables ;
	
	public static categoryConnector getInstance(){
		if (instance == null){
			instance = new categoryConnector();
		}
		return instance;
	}
	//add all the files we want to updated
	private  ArrayList<freebaseTable> InitializeFictionalUniverseFiles(){
		// all the tables we want to add
		 ArrayList<freebaseTable> files = new ArrayList<freebaseTable>();
		files.add(new freebaseTable(freeBaseURL + "character_species.tsv",2,"Found in fictional Universes: ","Fictional Species"));
		files.add(new freebaseTable(freeBaseURL + "character_powers.tsv",2,"Characters with this ability: ","Fictional Abilities"));
		files.add(new freebaseTable(freeBaseURL + "character_occupation.tsv",2,"Characters With this occupation: ","Fictional Occupations"));
		files.add(new freebaseTable(freeBaseURL + "ethnicity_in_fiction.tsv",2,"Characters of this ethnicity: ","Fictional Ethnicities"));
		files.add(new freebaseTable(freeBaseURL + "fictional_character.tsv",7,"Occupation: ","Fictional Characters"));
		files.add(new freebaseTable(freeBaseURL + "fictional_character_creator.tsv",2,"Characters Created by this creator: ","Fictional Creators"));
		files.add(new freebaseTable(freeBaseURL + "fictional_creature.tsv",4,"Creature species: ","Fictional Creatures"));
		files.add(new freebaseTable(freeBaseURL + "fictional_job_titles.tsv",2,"Characters With this job: ","Fictional Jobs"));
		files.add(new freebaseTable(freeBaseURL + "fictional_language.tsv",2,"Where spoken: ","Fictional Languages"));
		files.add(new freebaseTable(freeBaseURL + "fictional_object.tsv",4,"Owned by: ","Fictional Objects"));
		files.add(new freebaseTable(freeBaseURL + "fictional_organization.tsv",2,"Members: ","Fictional Organizations"));
		files.add(new freebaseTable(freeBaseURL + "fictional_plant.tsv",2,"Featued in universe: ","Fictional Plants"));
		files.add(new freebaseTable(freeBaseURL + "fictional_substance.tsv",3,"Objects made from this substance: ","Fictional Substances"));
		files.add(new freebaseTable(freeBaseURL + "fictional_universe.tsv",3,"Characters from this universe: ","Fictional Universes"));
		files.add(new freebaseTable(freeBaseURL + "medical_condition_in_fiction.tsv",2,"Characters With this condition: ","Fictional Medical Conditions"));
		files.add(new freebaseTable(freeBaseURL + "school_in_fiction.tsv",2,"Students garduate from this school: ","Fictional Schools"));
		return files;
		
	}
	
	private categoryConnector(){
		//insert all fictional tables into tables
		tables = new ArrayList<freebaseTable>();
		Connection con = openConnection();
		Connection conn = openConnection();
		try {
			
			Table categories = tablesStructs.categories;
			Statement st = con.createStatement();
			String q = "SELECT * FROM " + categories.name;
			ResultSet rs = st.executeQuery(q);
			
			while(rs.next()){
				freebaseTable newTable = new freebaseTable(
						rs.getString(categories.columns[1]),
						rs.getInt(categories.columns[2]),
						rs.getString(categories.columns[3]),
						rs.getString(categories.columns[0]));
				tables.add(newTable);
			}
			ArrayList<freebaseTable> fictional = InitializeFictionalUniverseFiles();
			for (freebaseTable t1 : tables){
				for (freebaseTable t2 : fictional){
					if (t1.categoryName.equalsIgnoreCase(t2.categoryName)){
						fictional.remove(t2);
						break;
					}
				}
			}
			conn.setAutoCommit(false);
			Statement statement = conn.createStatement();
			for (freebaseTable t1 : fictional){
				tables.add(t1);
				statement.addBatch(buildCommand(t1));
				String v = "CREATE OR REPLACE VIEW " + t1.categoryName.replace(' ' , '_') +
				" AS SELECT `phraseId`, `name`, `category`, `hint`, `manualArticle`, `updater` FROM phrases, manualarticles WHERE " +
				"category = '" + t1.categoryName +"' AND `manualArticleId` = `matchingManualArticleId`";
				statement.addBatch(v);
				
			}
			statement.executeBatch();
			conn.commit();
			statement.clearBatch();
		} 
		catch (Exception e) {
			e.printStackTrace();
			} 
		finally{
			closeConnection(conn);
			closeConnection(con);
		}
	}
	
	public ArrayList<freebaseTable> getAllCategories(){
		return tables;
	}
	
	public String selectRandomCategory(){
		Random rand = new Random();
		return tables.get(rand.nextInt(tables.size() -1)).categoryName;
	}
		/*
		
	}*/
	
	private String buildCommand(freebaseTable t){
		String command = new String();
		Table categoriesTable = tablesStructs.categories;
		String legalName = t.categoryName.replace('\"', '\'');
		String legalHint = t.HintStr.replace('\"', '\'');
		command += "INSERT INTO " + categoriesTable.name +" VALUES (\"";
		command += legalName + "\",\"";
		command += t.fileName + "\",";
		command += t.hintColumns+ ",\"";
		command += legalHint + "\")";
		command += "ON DUPLICATE KEY UPDATE " + categoriesTable.columns[3] +"=" + categoriesTable.columns[3];
	return command;
	}

	 protected Connection openConnection(){
		return DBConnectionPool.getInstance().getConnection();
	}
	
	 protected void closeConnection(Connection conn){
		DBConnectionPool.getInstance().returnConnection(conn);
	} 
	
	public String addCategoryToDB(String category, String url, int hintCol, String hintPrefix){
		freebaseTable table = new freebaseTable(url, hintCol, hintPrefix, category);
		Connection con = openConnection();
		try {
			if (con == null){
				return "Error connecting to DB";
			}
			Table categories = tablesStructs.categories;
			Statement st = con.createStatement();
			//insert category to db
			String q = "INSERT INTO " + categories.name + " VALUES ('";
			q += category +  "','";
			q += url +  "',";
			q += hintCol +  ",'";
			q += hintPrefix +  "')";
			st.executeUpdate(q);
			
			//now create the view
			q = "CREATE OR REPLACE VIEW " + category.replace(' ' , '_') +
			" AS SELECT `phraseId`, `name`, `category`, `hint`, `manualArticle`, `updater` FROM phrases, manualarticles WHERE " +
			"category = '" + category +"' AND `manualArticleId` = `matchingManualArticleId`";
			st.executeUpdate(q);
		}
		catch (Exception e){
			System.out.println(e.toString());
			return "Error Executing command on DB :" + e.toString();
		}
		finally{
			 closeConnection(con);
		}
		Importer importer  = new Importer();
		return importer.importTable(table);
		
	}
	
	
	
}